package com.jk.controller;

import com.jk.bean.Emp;
import com.jk.bean.Log;
import com.jk.bean.Staff;
import com.jk.bean.User;
import com.jk.service.UserService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping("poi")
public class PoiController {

     @Resource
     private UserService userService;

    @Resource
    private MongoTemplate mongoTemplate;

    @RequestMapping("query")
    public void export(String ids, HttpServletResponse res) throws IOException {


        List<Staff> list = userService.queryPoi(ids);


        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("staff");
        // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);
        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        // 设置单元格内容
        cell.setCellValue("员工信息表");
        // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        // 在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        // 创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("id");
        row2.createCell(1).setCellValue("账号");
        row2.createCell(2).setCellValue("密码");

        int a = 2;
        for (Staff staff : list) {
            // 在sheet里创建第三行
            HSSFRow row3 = sheet.createRow(a++);
            row3.createCell(0).setCellValue(staff.getStaffId());
            row3.createCell(1).setCellValue(staff.getStaffName());
            row3.createCell(2).setCellValue(staff.getStaffPwd());
        }





        //将excel的数据写入文件
        ByteArrayOutputStream fos = null;
        byte[] retArr = null;
        try {
            fos = new ByteArrayOutputStream();
            wb.write(fos);
            retArr = fos.toByteArray();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                fos.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        OutputStream os = res.getOutputStream();
        try {
            res.reset();
            res.setHeader("Content-Disposition", "attachment; filename=agent_book.xls");//要保存的文件名
            res.setContentType("application/octet-stream; charset=utf-8");
            os.write(retArr);
            os.flush();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }
    @RequestMapping("queryUserPoi")
    public void queryUserPoi(String ids, HttpServletResponse res) throws IOException {


        List<User> list = userService.queryUserPoi(ids);


        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("staff");
        // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);
        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        // 设置单元格内容
        cell.setCellValue("员工信息表");
        // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        // 在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        // 创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("id");
        row2.createCell(1).setCellValue("账号");
        row2.createCell(2).setCellValue("昵称");
        row2.createCell(3).setCellValue("性别");
        row2.createCell(4).setCellValue("等级");
        row2.createCell(5).setCellValue("创建日期");
        row2.createCell(6).setCellValue("手机号");
        row2.createCell(7).setCellValue("会员状态");


SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String sex , state;

        int a = 2;
        for (User user : list) {

            if(user.getState()==1){
                state = "普通用户";
            }else if(user.getState()==2){
                state = "超级用户";
            }else {
                state = "未知";
            }

            if(user.getUserSex()==1){
                sex = "男";
            }else if (user.getUserSex()==2){
                sex = "女";
            }else {
                sex = "未知";
            }
            // 在sheet里创建第三行
            HSSFRow row3 = sheet.createRow(a++);
            row3.createCell(0).setCellValue(user.getUserId());
            row3.createCell(1).setCellValue(user.getUserAccout());
            row3.createCell(2).setCellValue(user.getNickName());
            row3.createCell(3).setCellValue(sex);
            row3.createCell(4).setCellValue(user.getGrade());
            row3.createCell(5).setCellValue(sdf.format(user.getAddTime()));
            row3.createCell(6).setCellValue(user.getPhone());
            row3.createCell(7).setCellValue(state);
        }





        //将excel的数据写入文件
        ByteArrayOutputStream fos = null;
        byte[] retArr = null;
        try {
            fos = new ByteArrayOutputStream();
            wb.write(fos);
            retArr = fos.toByteArray();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                fos.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        OutputStream os = res.getOutputStream();
        try {
            res.reset();
            res.setHeader("Content-Disposition", "attachment; filename=agent_book.xls");//要保存的文件名
            res.setContentType("application/octet-stream; charset=utf-8");
            os.write(retArr);
            os.flush();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

    @ResponseBody
    @PostMapping("import")
    public boolean addUser(@RequestParam("file") MultipartFile file) {
        boolean a = false;
        String fileName = file.getOriginalFilename();
        try {
            a = userService.batchImport(fileName, file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  a;
    }



    @RequestMapping("queryMongo")
    public void queryMongo(String ids, HttpServletResponse res) throws IOException {

        Query query = new Query();
        String[] split = ids.split(",");
        List<Log> list = new ArrayList<>();
        for (int i = 0; i < split.length; i++) {
            Log one = mongoTemplate.findById(split[i],Log.class);
            list.add(one);
        }

        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("staff");
        // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);
        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        // 设置单元格内容
        cell.setCellValue("用户登录信息表");
        // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        // 在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        // 创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("id");
        row2.createCell(1).setCellValue("用户名");
        row2.createCell(2).setCellValue("登录时间");
        row2.createCell(3).setCellValue("登录的主机名");
        row2.createCell(4).setCellValue("操作系统");
        row2.createCell(5).setCellValue("手机号");

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int a = 2;
        for (Log log : list) {
            // 在sheet里创建第三行
            HSSFRow row3 = sheet.createRow(a++);
            row3.createCell(0).setCellValue(log.getId());
            row3.createCell(1).setCellValue(log.getUserAccout());
            row3.createCell(2).setCellValue(sdf.format(log.getAddTime()));
            row3.createCell(3).setCellValue(log.getName());
            row3.createCell(4).setCellValue(log.getVersion());
            row3.createCell(5).setCellValue(log.getPhone());

        }





        //将excel的数据写入文件
        ByteArrayOutputStream fos = null;
        byte[] retArr = null;
        try {
            fos = new ByteArrayOutputStream();
            wb.write(fos);
            retArr = fos.toByteArray();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                fos.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        OutputStream os = res.getOutputStream();
        try {
            res.reset();
            res.setHeader("Content-Disposition", "attachment; filename=agent_book.xls");//要保存的文件名
            res.setContentType("application/octet-stream; charset=utf-8");
            os.write(retArr);
            os.flush();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }



    @ResponseBody
    @PostMapping("importMongo")
    public boolean importMongo(@RequestParam("file") MultipartFile file) {
        boolean a = false;
        String fileName = file.getOriginalFilename();
        try {
            a = userService.importMongo(fileName, file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  a;
    }


    @RequestMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response, HttpServletRequest request) throws Exception{

        //接收参数
        //     String accounttime = request.getParameter("accounttime");
        //查询数据，实际可通过传过来的参数当条件去数据库查询，在此我就用空集合（数据）来替代
        List<Emp> list = userService.exportExcel();

        //创建poi导出数据对象
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();

        //创建sheet页
        SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet页名字");
        //创建表头
        SXSSFRow headRow = sheet.createRow(0);
        //设置表头信息
        headRow.createCell(0).setCellValue("序号");
        headRow.createCell(1).setCellValue("id");
        headRow.createCell(2).setCellValue("姓名");
        headRow.createCell(3).setCellValue("年龄");

        int x = 1;
        // 遍历上面数据库查到的数据
        for (Emp pm : list) {
            //序号

            //填充数据
            SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            //序号
            dataRow.createCell(0).setCellValue(x);
            dataRow.createCell(1).setCellValue(pm.getId());
            //看你实体类在进行填充
            dataRow.createCell(2).setCellValue(pm.getName());
            dataRow.createCell(3).setCellValue(pm.getAge());
            x++;
        }

        // 下载导出
        String filename = "导出excel表格名字";
        // 设置头信息
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        //一定要设置成xlsx格式
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename + ".xlsx", "UTF-8"));
        //创建一个输出流
        ServletOutputStream outputStream = response.getOutputStream();
        //写入数据
        sxssfWorkbook.write(outputStream);

        // 关闭
        outputStream.close();
        sxssfWorkbook.close();
    }




}
